package data;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

import javax.xml.bind.JAXBException;
import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * User: Vladimir
 * Date: 17.11.12 Time: 19:22
 */
public class ExtramobiExcel {
    public static FormulaEvaluator evaluator;
    public static Pattern pattern = Pattern.compile("\\[[^\\]]*\\]");

    public static Map<String, String> commonProps;
    public static final String DATA_PATH = "data/extramobi";

    public static void main(String[] args) throws IOException, IllegalAccessException, JAXBException, InvalidImport {
//        String outPath = "data/import/test/";
        String outPath = ExtramobiNew.OUT_FOLDER;
        commonProps = ExtramobiNew.createTemplateProps(DATA_PATH + "/common.properties");
        String productUrl = commonProps.get("\\#\\{common\\.product-url\\}");
        boolean processSitemap = true;
        Map<String, List<ItemData>> data = parseItemData(DATA_PATH);
        for (String dataPath : data.keySet()) {
            List<ItemData> itemDataList = data.get(dataPath);
            for (ItemData item : itemDataList) {
                if (item.category || !item.popular) {
                    continue;
                }
                if (item.models == null) {
                    ExtramobiNew.ModelItem modelItem = new ExtramobiNew.ModelItem();
                    modelItem.modelName = (item.brand + " " + item.model).toLowerCase().trim();
                    modelItem.brandName = item.brand;
                    modelItem.count = item.count;
                    modelItem.price = item.price;
                    modelItem.url = productUrl + item.url;
                    modelItem.name = item.name;
                    modelItem.article = item.article;
                    modelItem.imgUrl = item.photos.get(0).split(",")[0];
                    ExtramobiNew.putModelItem(modelItem);
                } else {
                    for (String model : item.models) {
                        ExtramobiNew.ModelItem modelItem = new ExtramobiNew.ModelItem();
                        modelItem.modelName = (item.brand + " " + model).toLowerCase().trim();
                        modelItem.brandName = item.brand;
                        modelItem.count = item.count;
                        modelItem.price = item.price;
//                        modelItem.url = productUrl + DataHelper.codeInURL(item.brand + " " + model.toLowerCase());
                        modelItem.url = productUrl + replaceModelUrl(item, model, item.url);
                        modelItem.name = replaceModel(item, model, item.name);
                        modelItem.article = item.article;
                        modelItem.imgUrl = item.photos.get(0).split(",")[0];
                        ExtramobiNew.putModelItem(modelItem);
                        if (processSitemap) {
                            ExtramobiNew.addToSitemap(productUrl + modelItem.url);
                        }
                    }
                }
                if (processSitemap) {
                    ExtramobiNew.addToSitemap(productUrl + item.url);
                }
            }
        }
        for (String dataPath : data.keySet()) {
            List<ItemData> itemDataList = data.get(dataPath);
            for (ItemData item : itemDataList) {
                filterItem(item, null);
            }
        }
        for (String file : data.keySet()) {
            PrintStream out = new PrintStream(new FileOutputStream(outPath + file));
            out.println(CSVData.getHeader());
            List<String> printedModels = new ArrayList<String>();
            for (ItemData item : data.get(file)) {
                if (!item.category && item.models != null && item.model.equals(item.models.get(0))) {
                } else {
                    out.println(item);
                }
                if (item.modelsData != null) {
                    for (ItemData modelItem : item.modelsData) {
                        if (printedModels.contains(modelItem.name.toLowerCase().trim())) {
                            System.out.println("Already processed: " + modelItem.name);
                        } else {
                            printedModels.add(modelItem.name.toLowerCase().trim());
                            out.println(modelItem);
                        }
                    }
                }
            }
            out.close();
        }
        ExtramobiNew.printSitemap();
        ValidateImport.validate(outPath);
        ValidateImport.validate(data);
    }

    public static Map<String, List<ItemData>> parseItemData(String inPath, List<String> exceptionList, boolean parseCategories) throws IOException, IllegalAccessException {
        Map<String, List<ItemData>> data = new HashMap<String, List<ItemData>>();
        List<String> cats = Arrays.asList(new File(inPath).list());
        Collections.sort(cats, new OSOrder());
        for (String inFile : cats) {
            Map<String, List<ItemData>> dataByCat = parseItems(inFile, exceptionList, parseCategories);
            if (dataByCat != null) {
                data.putAll(dataByCat);
            }
        }
        return data;
    }

    public static Map<String, List<ItemData>> parseItems(String inFile, List<String> exceptionList, boolean parseCategories, String... brands) throws IOException {
        Map<String, List<ItemData>> data = new HashMap<String, List<ItemData>>();
        if (!inFile.endsWith(".xls") || exceptionList.contains(inFile)) {
            return null;
        }
        InputStream in = new FileInputStream(DATA_PATH + "/" + inFile);
        HSSFWorkbook wb = new HSSFWorkbook(in);
        evaluator = wb.getCreationHelper().createFormulaEvaluator();
        String postfix = inFile.split("\\.")[0];
        HSSFSheet sheet;
        List<String> sheets = new ArrayList<String>();
        int i = 0;
        while (true) {
            try {
                String brand = wb.getSheetName(i++);
                if (!isInBrandsList(brand, brands)) {
                    continue;
                }
                sheets.add(brand);
            } catch (IllegalArgumentException e) {
                break;
            }
        }
        Collections.sort(sheets);
        for (String brand : sheets) {
            String outFile = postfix + "-" + brand + ".txt";
            sheet = wb.getSheet(brand);
            List<String> headers = getHeader(sheet);
            Iterator<Row> rowIterator = sheet.rowIterator();
            List<ItemData> itemDataList = new ArrayList<ItemData>();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (row.getRowNum() > 0) {
                    ItemData item = parseItem(row, parseCategories, headers);
                    if (item != null) {
                        if (!item.category && item.models != null && item.models.size() == 1) {
                            ItemData compatibleItem = searchCompatible(itemDataList, item.models.get(0));
                            if (compatibleItem != null) {
                                item.models = new ArrayList<String>(compatibleItem.models);
                            }
                        }
                        if (item.modelsData != null) {
                            for (ItemData modelItem : item.modelsData) {
                                itemDataList.add(modelItem);
                            }
                        }
                        itemDataList.add(item);
                    }
                }
            }
            data.put(outFile, itemDataList);
//            } catch (IllegalArgumentException e) {
//                break;
//            }
        }
        return data;
    }

    private static ItemData searchCompatible(List<ItemData> items, String name) {
        for (ItemData item : items) {
            if (item.model.equals(name)) {
                return item;
            }
        }
        return null;
    }

    public static boolean isInBrandsList(String brandFile, String[] brands) {
//        if (brands.length == 0) {
//            return true;
//        }
        if (brands[0].equals("All")) {
            return true;
        }
        for (String brand : brands) {
            if (brandFile.toLowerCase().contains(brand.toLowerCase())) {
                return true;
            } else if (brandFile.toLowerCase().contains("se") && brand.equalsIgnoreCase("sony ericsson")) {
                return true;
            }
        }
        return false;
    }


    public static Map<String, List<ItemData>> parseItems(String inFile, List<String> exceptionList, boolean parseCategories) throws IOException {
        return parseItems(inFile, exceptionList, parseCategories, "All");
    }

    public static ItemData parseItem(Row row, boolean parseCategories, List<String> headers) {
        int lastIdx = headers.lastIndexOf("Фотография");
        ItemData item = new ItemData();
        int column = 0;
        item.brand = getCellValue(row, column++);
        item.model = getCellValue(row, column++);
        item.article = getCellValue(row, column++);
        item.engname = getCellValue(row, column++);
        item.name = getCellValue(row, column++);
        item.url = DataHelper.codeInURL(getCellValue(row, column++));
        try {
            item.price = Integer.parseInt(getCellValue(row, column++));
            item.oldPrice = Integer.parseInt(getCellValue(row, column++));
            item.count = Integer.parseInt(getCellValue(row, column++));
        } catch (NumberFormatException e) {
            item.category = true;
            column += 2;
        }
        if (item.category && !parseCategories) {
            return null;
        }
        item.desc = toLine(getCellValue(row, column++));
        item.shortdesc = getCellValue(row, column++);
        column++;
        item.title = getCellValue(row, column++);
        item.metakey = getCellValue(row, column++);
        item.metadesc = getCellValue(row, column++);
        item.photos = new ArrayList<String>();
        for (int j = column; j < lastIdx; j++) {
            item.photos.add(getCellValue(row, column++));
        }
        List<String> models = null;
        Map<String, String> props = new HashMap<String, String>();
        for (int j = column; j < headers.size(); j++) {
            if (headers.get(j).equals("Список моделей телефонов")) {
                models = Arrays.asList(getCellValue(row, j).split(","));
            } else if (!headers.get(j).equals("Художественное описание") &&
                    !headers.get(j).equals("Оригинальный") &&
                    !headers.get(j).equals("Фотография")) {
                item.props.put(headers.get(j), getCellValue(row, j));
            }
            props.put(headers.get(j), getCellValue(row, j));
        }
        item.models = models;
        ExtramobiNew.processOrig(item, props);
        if (!item.category && headers.size() > column + 1 && headers.get(++column).equals("Популярный товар")) {
            item.popular = Boolean.parseBoolean(getCellValue(row, column));
        }
        if (item.category) {
            ExtramobiNew.putCatSort(item.name);
            item.sort = ExtramobiNew.getSort(item.name);
        } else {
            item.sort = ExtramobiNew.getSort(item.name, item.article);
        }
        return item;
    }


    public static Map<String, List<ItemData>> parseItemData(String inPath) throws IOException, IllegalAccessException {
        return parseItemData(inPath, new ArrayList<String>(), true);
    }

    private static void filterItem(ItemData item, String model) throws IllegalAccessException, IOException {
//        if (item.category) {
//            return;
//        }
        int chooseIdx = model == null ? 0 : 1;
        if (model == null) {
            ExtramobiNew.processOrig(item);
        }
        Field[] fields = item.getClass().getFields();
        if (item.models != null && item.models.size() > 0) {
            item.modelsData = new ArrayList<ItemData>();
            for (String itemModel : item.models) {
                ItemData cloneItem = item.getCopy();
                cloneItem.models = null;
                filterItem(cloneItem, itemModel);
                item.modelsData.add(cloneItem);
            }
        }
        for (Field field : fields) {
            if (!field.getType().equals(String.class)) {
                continue;
            }
            String fieldValue = (String) field.get(item);
            if (fieldValue != null && fieldValue.length() > 1) {
                Map<String, String> chooseValues = new HashMap<String, String>();
                String filteredValue = fieldValue;
                Matcher matcher = pattern.matcher(filteredValue);
                while (matcher.find()) {
                    String chooseValue = filteredValue.substring(matcher.start() + 1, matcher.end() - 1).split("\\|")[chooseIdx];
                    chooseValues.put(filteredValue.substring(matcher.start(), matcher.end()), chooseValue);
                }
                for (String replaceString : chooseValues.keySet()) {
                    filteredValue = filteredValue.replace(replaceString, chooseValues.get(replaceString));
                }
                filteredValue = filteredValue.replaceAll("\\$\\{model\\.titlelist\\}", ExtramobiNew.getTitleList(item.models));
                filteredValue = filteredValue.replaceAll("\\$\\{model\\.shortlist\\}", ExtramobiNew.getShortList(item.models));
                filteredValue = filteredValue.replaceAll("\\$\\{model\\.reflist\\}", ExtramobiNew.getRefList(item));
                filteredValue = filteredValue.replaceAll("\\$\\{model\\.order\\.list\\}", ExtramobiNew.getModelList(item.brand + " " + model));
                filteredValue = filteredValue.replaceAll("\\$\\{item\\.order\\.list\\}", ExtramobiNew.getModelList(item.brand + " " + item.model));

                for (String key : commonProps.keySet()) {
                    filteredValue = filteredValue.replaceAll(key, commonProps.get(key));
                }

                if (model != null) {
                    filteredValue = filteredValue.replaceAll("\\$\\{model\\.name\\}", item.brand + " " + model);
                    if (!field.getName().equals("model")) {
                        filteredValue = replaceModel(item, model, filteredValue);
                        if (field.getName().equals("url")) {
                            filteredValue = replaceModelUrl(item, model, filteredValue);
                        }
                    }
                    filteredValue = filteredValue.replaceAll("\\$\\{item\\.name\\}", item.brand + " " + item.model);
                }
                filteredValue = filteredValue.replaceAll("\\$\\{item\\.props\\}", ExtramobiNew.getPropsList(item.props));
                field.set(item, filteredValue);
            }
        }
    }

    private static String replaceModel(ItemData item, String model, String nameToReplace) {
        return nameToReplace.replaceAll(item.model, model);
    }

    public static String replaceModelUrl(ItemData item, String model, String urlToFilter) {
        return urlToFilter.replaceAll(DataHelper.codeInURL(item.model.toLowerCase()), DataHelper.codeInURL(model.toLowerCase()).trim());
    }

    public static String getCellValue(Row row, int column) {
        Cell cell = row.getCell(column);
        if (cell == null) {
            return "";
        }
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return evaluator.evaluate(cell).getStringValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return (int) cell.getNumericCellValue() + "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        return cell.getStringCellValue();
    }

    private static List<String> getHeader(HSSFSheet sheet) {
        HSSFRow row = sheet.getRow(0);
        List<String> headers = new ArrayList<String>();
        Iterator<Cell> iterator = row.cellIterator();
        while (iterator.hasNext()) {
            Cell cell = iterator.next();
            headers.add(cell.getStringCellValue());
        }
        return headers;
    }

/*
    public static void main1(String[] args) throws BiffException, IOException {
        String path = "data/extramobi/test/phone.xls";
        String outPath = "data/import/test.txt";
        PrintStream out = new PrintStream(new FileOutputStream(outPath), false, ExtramobiNew.IMPORT_FILE_ENCODING);
        Workbook workbook = Workbook.getWorkbook(new File(path));
        Sheet sheet = workbook.getSheet(0);
        List<String> headers = new ArrayList<String>();
        for (int column = 0; column < sheet.getColumns(); column++) {
            String cell = sheet.getCell(column, 0).getContents();
            headers.add(cell);
        }
//        for (String header : headers) {
//            System.out.println(header);
//        }
        List<CSVData> csvRows = new ArrayList<CSVData>();
        List<String> headerItems = CSVData.getHeaderItems();

        int startIdx = headers.indexOf("Артикул");
        int endIdx = headers.lastIndexOf("Фотография");
//        for (String item : headerItems) {
//            System.out.println(item);
//        }
        out.println(CSVData.getHeader());
        for (int row = 1; row < sheet.getRows(); row++) {
            ItemData item = new ItemData();
            int column = 0;
            item.brand = sheet.getCell(column++, row).getContents();
            item.model = sheet.getCell(column++, row).getContents();
            item.article = sheet.getCell(column++, row).getContents();
            item.engname = sheet.getCell(column++, row).getContents();
            item.name = sheet.getCell(column++, row).getContents();
            item.url = sheet.getCell(column++, row).getContents();
            try {
                item.price = Integer.parseInt(sheet.getCell(column++, row).getContents());
                item.oldPrice = Integer.parseInt(sheet.getCell(column++, row).getContents());
                item.count = Integer.parseInt(sheet.getCell(column++, row).getContents());
            } catch (NumberFormatException e) {
                item.category = true;
                column += 2;
            }
            item.desc = toLine(sheet.getCell(column++, row).getContents());
            item.shortdesc = sheet.getCell(column++, row).getContents();
            item.sort = sheet.getCell(column++, row).getContents();
            item.title = sheet.getCell(column++, row).getContents();
            item.metakey = sheet.getCell(column++, row).getContents();
            item.metadesc = sheet.getCell(column++, row).getContents();
            item.photos = new ArrayList<String>();
            for (int i = column; i < endIdx; i++) {
                item.photos.add(sheet.getCell(i, row).getContents());
            }
            item.sort = item.sort.replace("${sort}", row + "");
            out.println(item);
//            CSVData data = new CSVData();
//            for (int column = 0; column < sheet.getColumns(); column++) {
//                String cell = sheet.getCell(column, row).getContents();
//                System.out.print(cell + "\t");
//            }
//            String brand = sheet.getCell(0, row).getContents();
//            String brand = sheet.getCell(, row).getContents();
//            System.out.println();
//            csvRows.add(data);
        }
        out.close();
    }
*/

    private static String toLine(String s) {
        return s.replaceAll("\\n", "");
    }

    private static class OSOrder implements Comparator<String> {
        @Override
        public int compare(String file1, String file2) {
            file1 = file1.substring(0, file1.length() - 4);
            file2 = file2.substring(0, file2.length() - 4);
            return file1.compareTo(file2);
        }
    }
}